﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;
using System.Data.SqlClient;

namespace bdnt_pets_system_Bo.DivisionManager
{
    public class DivisionManager
    {
        AccessHelperSQL accessHelper = new AccessHelperSQL();

        public List<Tbl_Department> ConvertTableToObjs(DataTable dt)
        {
            List<Tbl_Department> returnlist = new List<Tbl_Department>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Department
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                    DeptNo = dr["DeptNo"] == DBNull.Value ? "" : dr["DeptNo"].ToString(),
                    DeptName = dr["DeptName"] == DBNull.Value ? "" : dr["DeptName"].ToString(),
                    DeptType = dr["DeptType"] == DBNull.Value ? 0 : Int32.Parse(dr["DeptType"].ToString()),
                    ManagerNo = dr["ManagerNo"] == DBNull.Value ? "" : dr["ManagerNo"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString()
                });
            }
            return returnlist;
        }

        public List<Tbl_Department> SelectDepartment(string DName) {
            var sql = string.Format(@"select * from Tbl_Department");
            if (!string.IsNullOrEmpty(DName))
            {
                sql = string.Format(@"select * from Tbl_Department where DeptName like '%{0}%'", DName);
            }
            var dt = accessHelper.ExcuteDataTable(sql);
            return ConvertTableToObjs(dt);
        }

        public Tbl_Department GetDepartment(Int32 objId)
        {
            Tbl_Department td = new Tbl_Department();
            string sql = string.Format("select * from Tbl_Department where Id={0}", objId);
            var dt=accessHelper.ExcuteDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                td = new Tbl_Department()
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                    DeptNo = dr["DeptNo"] == DBNull.Value ? "" : dr["DeptNo"].ToString(),
                    DeptName = dr["DeptName"] == DBNull.Value ? "" : dr["DeptName"].ToString(),
                    DeptType = dr["DeptType"] == DBNull.Value ? 0 : Int32.Parse(dr["DeptType"].ToString()),
                    ManagerNo = dr["ManagerNo"] == DBNull.Value ? "" : dr["ManagerNo"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString()
                };
            }
            return td;
        }

        public bool SaveDept(Tbl_Department td)
        {
            string sql = "";
            if (td.Id == 0)
            {
                sql = string.Format(@"INSERT INTO [Tbl_Department]([DeptNo],[DeptName],[DeptType],[ManagerNo],[Remark])
             VALUES('{0}','{1}','{2}','{3}','{4}')", td.DeptNo, td.DeptName, td.DeptType, td.ManagerNo, td.Remark);
                return accessHelper.ExcultData(sql);
            }
            else
            {
                sql = string.Format(@"UPDATE Tbl_Department Set DeptNo='{0}',DeptName='{1}',DeptType='{2}',ManagerNo='{3}',Remark='{4}'  WHERE Id='{5}'",
                  td.DeptNo, td.DeptName, td.DeptType, td.ManagerNo, td.Remark,td.Id);
                return accessHelper.ExcultData(sql);
            }
        }

        public bool DeleteDepartment(Int32 objId) {
            string sql = string.Format(@"delete from [Tbl_Department] where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }


    }
}
